using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Microsoft.ConnectionEngine.Registers.Core.ServiceProvider.Common;
using Microsoft.ConnectionEngine.Registers.Core.ServiceProvider.Common.DataSets;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Configuration;

namespace Microsoft.ConnectionEngine.Registers.Core.ServiceProvider.DataAccess
{
    public class ServiceProviderType
    {

        #region Get/search methods

        /// <summary>
        /// Gets a specific ServiceProviderType by ID
        /// </summary>
        /// <param name="ServiceProviderTypeID">The id of the serviceprovidertype to retrieve</param>
        /// <returns>A typed dataset representing the requested serviceprovidertype</returns>
        [CLSCompliant(false)]
        public static dvRefData ServiceProviderTypeSelectItem(string serviceProviderTypeID)
        {
            dvRefData ds = new dvRefData();
            string SQL = "ServiceProviderTypeSelectByID";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            ds.ServiceProviderType.Load(db.ExecuteReader(SQL, serviceProviderTypeID));
            return ds;
        }

        /// <summary>
        /// Returns true if a row ServiceProviderType exists
        /// </summary>
        /// <param name="ServiceProviderTypeDescription"></param>
        /// <returns>True if successful</returns>
        public static int ServiceProviderTypeExists(string ServiceProviderTypeDescription)
        {
            
            string SQL = "ServiceProviderTypeExists";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            
            return Convert.ToInt32(db.ExecuteScalar(SQL, ServiceProviderTypeDescription));
           
        }


        /// <summary>
        /// Gets all serviceprovidertypes
        /// </summary>
        /// <returns>A typed dataset representing the all serviceprovidertypes</returns>
        [CLSCompliant(false)]
        public static dsProviderType ServiceProviderTypeSearch(string displayName, string serviceProviderTypeCode, string poolId, bool inPool)
        {
            dsProviderType ds = new dsProviderType();
            string SQL;

            if (inPool)
                SQL = "ServiceProviderTypeSearch";
            else
                SQL = "ServiceProviderTypeSearchNotIn";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            //ds.ServiceProviderType.Load(db.ExecuteReader(SQL));
            
            if (string.IsNullOrEmpty(poolId))
                ds.ServiceProviderType.Load(db.ExecuteReader(SQL, displayName, serviceProviderTypeCode, System.DBNull.Value), LoadOption.OverwriteChanges);
            else
                ds.ServiceProviderType.Load(db.ExecuteReader(SQL, displayName, serviceProviderTypeCode, poolId), LoadOption.OverwriteChanges);
            return ds;
        }


        #endregion

        #region Check in/out methods

        /// <summary>
        /// Checks out a serviceprovidertype
        /// </summary>
        /// <param name="serviceProviderTypeID">The serviceprovidertype to be locked</param>
        /// <param name="userID">The user that is checking the object out</param>
        /// <returns>True if successful)</returns>
        public static bool ServiceProviderTypeLock(string serviceProviderTypeID, string userID)
        {
            bool result = true;
            string SQL = "ServiceProviderTypeLock";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            if (db.ExecuteNonQuery(SQL, serviceProviderTypeID, userID) <= 0)
                result = false;

            return result;
        }

        /// <summary>
        /// Unlocks a serviceprovidertype
        /// </summary>
        /// <param name="serviceProviderTypeID">The ServiceProviderType to be unlocked</param>
        /// <returns>True if successful)</returns>
        public static bool ServiceProviderTypeUnlock(string serviceProviderTypeID)
        {
            bool result = true;
            string SQL = "ServiceProviderTypeUnlock";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            if (db.ExecuteNonQuery(SQL, serviceProviderTypeID) <= 0)
                result = false;

            return result;
        }


        /// <summary>
        /// Checks in all ServiceProviderTypes checked out by a user
        /// </summary>
        /// <param name="checkoutproviderid">The provider that is checking in its ServiceProviderTypes</param>
        /// <param name="userID">The user that is unlocking all their ServiceProviderTypes</param>
        /// <returns>True if successful)</returns>
        public static bool ServiceProviderTypeUnlockAll(string userID)
        {
            bool result = true;
            string SQL = "ServiceProviderTypeUnlockAll";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            db.ExecuteNonQuery(SQL, userID);
                
            return result;
        }


        /// <summary>
        /// Tests if a serviceprovidertype is locked
        /// </summary>
        /// <param name="serviceprovidertypeid">The serviceprovidertype to be tested</param>
        /// <returns>True if checked out</returns>
        public static bool ServiceProviderTypeIsLocked(string serviceProviderTypeID)
        {
            bool result = false;
            string SQL = "ServiceProviderTypeIsLocked";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            if (Convert.ToInt32(db.ExecuteScalar(SQL, serviceProviderTypeID)) != 0)
                result = true;

            return result;
        }


        /// <summary>
        /// Tests if a serviceprovidertype is checked out by a user
        /// </summary>
        /// <param name="serviceprovidertypeid">The serviceprovidertype that is to to be tested</param>
        /// <param name="userID">The user that may have the object locked</param>
        /// <returns>True if successful</returns>
        public static bool ServiceProviderTypeIsLockedByUser(string serviceProviderTypeID, string userID)
        {
            bool result = false;
            string SQL = "ServiceProviderTypeIsLockedByUser";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            if (Convert.ToInt32(db.ExecuteScalar(SQL, serviceProviderTypeID,userID)) != 0)
                result = true;

            return result;
        }

        #endregion

        #region Delete methods

        /// <summary>
        /// Deletes a serviceprovidertype
        /// </summary>
        /// <param name="serviceprovidertypeid">The serviceprovidertype to delete</param>
        /// <returns>True if successful</returns>
        public static bool ServiceProviderTypeDelete(string serviceProviderTypeID)
        {
            bool result = true;
            string SQL = "ServiceProviderTypeDelete";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            if (db.ExecuteNonQuery(SQL, serviceProviderTypeID) <= 0)
                result = false;

            return result;
        }

        #endregion

        #region  Update/Insert methods

        /// <summary>
        /// Inserts a new ServiceProviderType
        /// </summary>
        /// <returns>A new ID if successful, otherwise empty string</returns>
        public static string ServiceProviderTypeInsert
        (
            string displayName,
            string serviceProviderTypeCode
        )
        {
            string result = string.Empty;
            string SQL = "ServiceProviderTypeInsert";


            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            result = db.ExecuteScalar(SQL, displayName, serviceProviderTypeCode).ToString();

            return result;
        }

        /// <summary>
        /// Updates an existing ServiceProviderType
        /// </summary>
        /// <returns>True if successful</returns>
        public static bool ServiceProviderTypeUpdate
        (
            string serviceProviderTypeID,
            string displayName,
            string serviceProviderTypeCode
        )
        {
            bool result = true;
            string SQL = "ServiceProviderTypeUpdate";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            if (db.ExecuteNonQuery(SQL, serviceProviderTypeID, displayName, serviceProviderTypeCode) <= 0)
                result = false;

            return result;
        }

        #endregion
    }
}
